﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_leave_query_export')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_leave_query_export

GO


-- =============================================
-- Author:      
-- Create date: 2020-08-04 13:57:54
-- Database:    [scm_main-hysj-test]
-- Description: 请假管理-请假列表记录导出
-- =============================================

CREATE PROCEDURE bd_proc_leave_query_export
  @dateBegin DATE,
  @dateEnd  DATE,
  @depts  VARCHAR(MAX) = '',    --提交人部门权限
  @userNameOrNo VARCHAR(100) = '',  --申请人工号或姓名
  @applierNameOrNo  VARCHAR(100) = '', --提交人工号或姓名
  @dateType  INT = 1,    --1：按出勤时间查询 2：按提交时间查询
  @orderNo VARCHAR(20) = '',
  @operateId  BIGINT,   --当前登录人
  @isPage  INT = 1,
  @pageSize  INT = 50,
  @pageNumber  INT = 1,
  @rowCount  INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;


  CREATE TABLE #result
  (
    order_id BIGINT NOT NULL,
    order_no VARCHAR(20) NOT NULL,
    leave_id VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    people_num INT NOT NULL,
    total_time DECIMAL(8, 2) NOT NULL,
    remark VARCHAR(2000) NULL,
    submitter_id VARCHAR(255) NOT NULL,
    submitter_no NVARCHAR(20) NULL,
    submitter_name NVARCHAR(50) NULL,
    submitter_dept_id INT NULL,
    submitter_time DATETIME NOT NULL,
    apply_type INT NOT NULL,
    status INT NOT NULL,
    status_name VARCHAR(8) NOT NULL,
    last_approver_id VARCHAR(255) NULL,
    last_approver_no NVARCHAR(20) NULL,
    last_approver_name NVARCHAR(50) NULL,
    is_del INT NOT NULL,
    approval_time DATETIME NULL,
    remove_id VARCHAR(64) NULL,
    remove_no NVARCHAR(20) NULL,
    remove_name NVARCHAR(50) NULL,
    remove_time DATETIME NULL,
    return_remark VARCHAR(2000) NULL,
    begin_time VARCHAR(10) NULL,
    end_time VARCHAR(10) NULL,
    detail_id INT NOT NULL,
    user_serial BIGINT NOT NULL,
    user_no NVARCHAR(20) NOT NULL,
    user_name NVARCHAR(50) NULL,
    user_dep INT NULL,
    dep_name NVARCHAR(50) NULL,
    bh VARCHAR(20) NULL,
    kssj DATETIME NOT NULL,
    jssj DATETIME NOT NULL,
    jsjg INT NOT NULL,
    jsjg_hour DECIMAL(8, 2) NULL,
    gly_no VARCHAR(10) NULL,
    shenhe VARCHAR(10) NULL,
    lx INT NULL,
    czrq DATETIME NULL,
    leave_length DECIMAL(20, 1) NULL,
    Business_purposes VARCHAR(1000) NULL,
    in_Place VARCHAR(1000) NULL,
    accommodation VARCHAR(10) NULL,
    ACCOMMODATION_DAYS DECIMAL(10, 1) NULL,
    schedule INT NULL,
    img_path VARCHAR(500) NULL,
    whether_validity INT NULL,
    is_deduct VARCHAR(1) NULL,
    detail_remove_user VARCHAR(64) NULL,
    detail_remove_time DATETIME NULL,
    detail_return_remark VARCHAR(2000) NULL,
    leave_type_name NVARCHAR(10) NULL,
    approver1 VARCHAR(50) NULL,
    approver1_no NVARCHAR(20) NULL,
    approver1_name NVARCHAR(50) NULL,
    approver2 VARCHAR(50) NULL,
    approver2_no NVARCHAR(20) NULL,
    approver2_name NVARCHAR(50) NULL,
    settle_year INT NOT NULL,
    settle_month INT NOT NULL
  )

  DECLARE @sql VARCHAR(MAX) = '', @sql2 VARCHAR(MAX) = '', @submitDeptId BIGINT

  SELECT @sql = '
    INSERT INTO #result (order_id, order_no, leave_id, start_date, end_date, people_num, total_time, remark, submitter_id, submitter_no, submitter_name, submitter_dept_id, submitter_time, apply_type, status, status_name, last_approver_id, last_approver_no, last_approver_name, is_del, approval_time, remove_id, remove_no, remove_name, remove_time, return_remark, begin_time, end_time, detail_id, user_serial, user_no, user_name, user_dep, dep_name, bh, kssj, jssj, jsjg, jsjg_hour, gly_no, shenhe, lx, czrq, leave_length, Business_purposes, in_Place, accommodation, ACCOMMODATION_DAYS, schedule, img_path, whether_validity, is_deduct, detail_remove_user, detail_remove_time, detail_return_remark, leave_type_name, approver1, approver1_no, approver1_name, approver2, approver2_no, approver2_name, settle_year, settle_month)
    SELECT order_id, order_no, leave_id, start_date, end_date, people_num, total_time, remark, submitter_id, submitter_no, submitter_name, submitter_dept_id, submitter_time, apply_type, status, status_name, last_approver_id, last_approver_no, last_approver_name, is_del, approval_time, remove_id, remove_no, remove_name, remove_time, return_remark, begin_time, end_time, detail_id, user_serial, user_no, user_name, user_dep, dep_name, bh, kssj, jssj, jsjg, jsjg_hour, gly_no, shenhe, lx, czrq, leave_length, Business_purposes, in_Place, accommodation, ACCOMMODATION_DAYS, schedule, img_path, whether_validity, is_deduct, detail_remove_user, detail_remove_time, detail_return_remark, leave_type_name, approver1, approver1_no, approver1_name, approver2, approver2_no, approver2_name, settle_year, settle_month 
    FROM bd_view_leave
    WHERE is_del = 0 '

  SELECT @sql2 = '
    UNION
    SELECT order_id, order_no, leave_id, start_date, end_date, people_num, total_time, remark, submitter_id, submitter_no, submitter_name, submitter_dept_id, submitter_time, apply_type, status, status_name, last_approver_id, last_approver_no, last_approver_name, is_del, approval_time, remove_id, remove_no, remove_name, remove_time, return_remark, begin_time, end_time, detail_id, user_serial, user_no, user_name, user_dep, dep_name, bh, kssj, jssj, jsjg, jsjg_hour, gly_no, shenhe, lx, czrq, leave_length, Business_purposes, in_Place, accommodation, ACCOMMODATION_DAYS, schedule, img_path, whether_validity, is_deduct, detail_remove_user, detail_remove_time, detail_return_remark, leave_type_name, approver1, approver1_no, approver1_name, approver2, approver2_no, approver2_name, settle_year, settle_month 
    FROM bd_view_leave
    WHERE is_del = 0 AND submitter_id = ' + CAST(@operateId AS VARCHAR(30))
  
  IF(ISNULL(@dateType, 0) = 1)
  BEGIN
    SELECT @sql += ' AND start_date >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND start_date < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 	
    SELECT @sql2 += ' AND start_date >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND start_date < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 
  END

  IF(ISNULL(@dateType, 0) = 2)
  BEGIN
    SELECT @sql += ' AND submitter_time >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND submitter_time < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 	
    SELECT @sql2 += ' AND submitter_time >=''' + CONVERT(VARCHAR(10), @dateBegin, 120) + ''' AND submitter_time < ''' + CONVERT(VARCHAR(10), DATEADD(DAY, 1, @dateEnd), 120) + ''' ' 	
  END

  IF(ISNULL(@depts, '') <> '')
  BEGIN
  	SELECT @sql += ' AND submitter_dept_id IN (' + @depts + ')'
  END

  --申请人工号、姓名
  IF(ISNULL(@userNameOrNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND (user_no LIKE ''%' + @userNameOrNo + '%'' OR user_name LIKE ''%' + @userNameOrNo + '%'')'
  	SELECT @sql2 += ' AND (user_no LIKE ''%' + @userNameOrNo + '%'' OR user_name LIKE ''%' + @userNameOrNo + '%'')'
  END

  --提交人工号、姓名
  IF(ISNULL(@applierNameOrNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND (submitter_no LIKE ''%' + @applierNameOrNo + '%'' OR submitter_name LIKE ''%' + @applierNameOrNo + '%'')'
  	SELECT @sql2 += ' AND (submitter_no LIKE ''%' + @applierNameOrNo + '%'' OR submitter_name LIKE ''%' + @applierNameOrNo + '%'')'
  END

  IF(ISNULL(@orderNo, '') <> '')
  BEGIN
  	SELECT @sql += ' AND order_no LIKE ''%' + @orderNo + '%'' '
  	SELECT @sql2 += ' AND order_no LIKE ''%' + @orderNo + '%'' '
  END

  EXEC(@sql + @sql2)


  SELECT @rowCount = COUNT(1) FROM #result 

  IF(ISNULL(@isPage, 0) = 0)
  BEGIN
  	SELECT * FROM #result ORDER BY swipe_time
  END ELSE
  BEGIN
    ;WITH result AS (
      SELECT *,row_no = ROW_NUMBER() OVER (ORDER BY submitter_time DESC) FROM #result 
    )
    SELECT * FROM result
    WHERE row_no BETWEEN (@pageNumber - 1) * @pageSize + 1 AND @pageNumber * @pageSize	
  END

  DROP TABLE #result

  RETURN @rowCount

  SET NOCOUNT OFF;
END